
alter FUNCTION f_finrptmulticolumns
(@ABYEARMON INT, @AEYEARMON INT, @binder INT, @filid int, @AMULTIBARID INT) 
RETURNS @TempTable TABLE 
  (VOUCHERID INT, YEARMON INT, VOUDATE DATETIME, SEQUNO INT
  , VOUWORDNO VARCHAR(20), SUBID INT, PSUBID INT
  , PBODEBIT INT, BRIEF VARCHAR(255), DEBIT Decimal(18,2), CREDIT Decimal(18,2)) 
AS
BEGIN
  DECLARE @VOUCHERID INT
  DECLARE @YEARMON INT
  DECLARE @VOUDATE DATETIME
  DECLARE @SEQUNO INT
  DECLARE @VOUWORDNO VARCHAR(20)
  DECLARE @SUBID INT
  DECLARE @PSUBID INT
  DECLARE @PBODEBIT INT
  DECLARE @BRIEF VARCHAR(255)
  DECLARE @DEBIT Decimal(18,2)
  DECLARE @CREDIT Decimal(18,2)
--select * from tfb_VOUCHERdtl
--select * from tfd_vouword
  DECLARE TempCursor1 CURSOR LOCAL FORWARD_ONLY STATIC OPTIMISTIC FOR 
    SELECT A.voucherid, A.vouYEARMONth, A.bDATE, A.orderno, W.NAME + '-' + CAST (A.VOUcherNO AS VARCHAR (16))
      , B.subjectid, M.PSUBID, M.PBODEBIT, B.explan, B.DEBITamt, B.CREDITamt 
      FROM tfb_VOUCHER A LEFT JOIN tfb_VOUCHERdtl B ON B.voucherid=A.voucherid 
      LEFT JOIN tfd_subject s ON s.subjectid=B.subjectid
      INNER JOIN f_finrptmulticolumnsdtl (@AMULTIBARID) M ON M.SUBID = B.subjectid 
      LEFT JOIN tfd_VOUWORD W ON W.id = A.VOUWORDID 
      WHERE A.voucherid>0 and (a.filid=@filid or @filid=-99) AND A.vouYEARMONth >= @ABYEARMON AND A.vouYEARMONth <= @AEYEARMON AND (A.binder_by is not null OR @binder=1) 
      ORDER BY A.vouYEARMONth, A.bDATE, A.orderno
  OPEN TempCursor1
  FETCH NEXT FROM TempCursor1 INTO @VOUCHERID, @YEARMON, @VOUDATE, @SEQUNO, @VOUWORDNO, @SUBID, @PSUBID, @PBODEBIT, @BRIEF, @DEBIT, @CREDIT
  WHILE (@@FETCH_STATUS = 0)
    BEGIN
      IF ((@BRIEF = '') OR (@BRIEF IS NULL))
        BEGIN
          SELECT @BRIEF = VS.explan FROM tfb_VOUCHERdtl VS LEFT JOIN tfb_VOUCHER V ON V.voucherid = VS.voucherid WHERE VS.bORDERNO = 1 AND V.voucherid = @VOUCHERID
        END
      INSERT INTO @TempTable VALUES (@VOUCHERID, @YEARMON, @VOUDATE, @SEQUNO, @VOUWORDNO, @SUBID, @PSUBID, @PBODEBIT, @BRIEF, @DEBIT, @CREDIT)
      FETCH NEXT FROM TempCursor1 INTO @VOUCHERID, @YEARMON, @VOUDATE, @SEQUNO, @VOUWORDNO, @SUBID, @PSUBID, @PBODEBIT, @BRIEF, @DEBIT, @CREDIT
    END
    CLOSE TempCursor1
    DEALLOCATE TempCursor1
    RETURN
  END
